import pymysql
from pymysql import Error
from datetime import datetime


# 1. 解析文本的函数（复用之前的逻辑）
def parse_warning_text(text):
    lines = [line.strip() for line in text.split('\n') if line.strip()]
    parsed_data = {}

    # 提取异常类型
    type_line = next(line for line in lines if line.startswith('异常类型:'))
    parsed_data['abnormal_type'] = type_line.replace('异常类型:', '').strip()

    # 提取异常原因
    info_line = next(line for line in lines if line.startswith('原因:'))
    parsed_data['reason'] = info_line.replace('原因:', '').strip()

    # 提取时间并转换格式
    time_line = next(line for line in lines if line.startswith('创建时间：'))
    time_str = time_line.replace('创建时间：', '').strip()
    parsed_time = datetime.strptime(time_str, '%Y-%m-%d-%H-%M-%S').strftime('%Y-%m-%d %H:%M:%S')
    parsed_data['created_at'] = parsed_time

    # 提取视频文件路径
    video_line = next(line for line in lines if line.startswith('视频URL:'))
    parsed_data['video_url'] = video_line.replace('视频URL:', '').strip()

    return parsed_data


# 2. 数据库插入函数（复用之前的逻辑）(异常记录信息表）
# 修改insert_into_mysql方法返回插入的ID
def insert_into_mysql(parsed_data, db_config):
    connection = None
    cursor = None
    try:
        connection = pymysql.connect(
            host=db_config['host'],
            user=db_config['user'],
            password=db_config['password'],
            database=db_config['database'],
            port=db_config['port'],
            charset='utf8mb4'
        )
        cursor = connection.cursor()
        insert_sql = """
            INSERT INTO abnormal_records (abnormal_type, reason, created_at)
            VALUES (%s, %s, %s)
        """
        cursor.execute(insert_sql, (
            parsed_data['abnormal_type'],
            parsed_data['reason'],
            parsed_data['created_at'],
        ))
        connection.commit()
        record_id = cursor.lastrowid
        print(f"数据插入成功！记录 ID：{record_id}")
        return record_id  # 返回插入的ID
    except Error as e:
        if connection:
            connection.rollback()
        print(f"数据库操作失败：{str(e)}")
        return False
    finally:
        if cursor:
            cursor.close()
        if connection:
            connection.close()


# 4.异常视频表
def insert_video_into_mysql(parsed_data, db_config, abnormal_id):
    connection = None
    cursor = None
    try:
        connection = pymysql.connect(
            host=db_config['host'],
            user=db_config['user'],
            password=db_config['password'],
            database=db_config['database'],
            port=db_config['port'],
            charset='utf8mb4'
        )
        cursor = connection.cursor()
        insert_sql = """
             INSERT INTO abnormal_videos (abnormal_id, video_url, created_at)
            VALUES (%s, %s, %s)
             """
        cursor.execute(insert_sql, (
            abnormal_id,
            parsed_data['video_url'],
            parsed_data['created_at'],
        ))
        connection.commit()
        print(f"视频数据插入成功！记录 ID：{cursor.lastrowid}")
        return True
    except Error as e:
        if connection:
            connection.rollback()
        print(f"数据库操作失败：{str(e)}")
        return False
    finally:
        if cursor:
            cursor.close()
        if connection:
            connection.close()


# 3. 修正后的文件读取函数（在 with 块内读取内容）
def read_warning_from_file(file_path):
    """从文件中读取警告文本内容（避免操作已关闭文件）"""
    try:
        # 在 with 块内完成读取，返回文本内容（字符串）
        with open(file_path, 'r', encoding='utf-8') as f:
            return f.read()  # 返回文件内容字符串，而非文件对象
    except FileNotFoundError:
        print(f"错误：文件 {file_path} 不存在")
        return None
    except IOError as e:
        print(f"文件读取错误：{str(e)}")
        return None


# 4. 主流程调用
if __name__ == "__main__":
    # 数据库配置（替换为你的实际配置）
    db_config = {
        'host': 'localhost',
        'user': 'root',
        'password': '123456',
        'database': 'project_db1',
        'port': 3306
    }

    # 读取文件内容（调用修正后的函数）
    raw_text = read_warning_from_file('././video_warning/alert_log.txt')  # 假设文件路径正确
    if not raw_text:
        print("无法读取文件内容，终止流程")
    else:
        # 解析并插入数据库
        parsed_data = parse_warning_text(raw_text)

        # 先插入异常记录并获取ID
        abnormal_id = insert_into_mysql(parsed_data, db_config)
        if insert_into_mysql:
            print("数据已成功插入数据库")
        if abnormal_id:
            # 确保异常记录插入成功后再插入视频记录
            insert_video_into_mysql(parsed_data, db_config, abnormal_id)
        if insert_video_into_mysql:
            print("视频已成功插入数据库")
